import logging
from datetime import datetime
from typing import List

from fastapi import APIRouter, Query, Depends, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

equipment_dict_router = APIRouter(prefix="/equipment/dict", tags=["设备类型管理"])


class DictSearch(PaginationBase):
    fdictname: str = Field(None, description="设备类型名称")


# todo 5.设备类型 设备信息
@equipment_dict_router.get("/list", summary="查询设备类型")
def list_equipment_dict(dict: DictSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from s_ts_equipment_dict where 1=1"
    if dict.fdictname is not None:
        sql = sql + " and fdictname like concat('%', :fdictname ,'%')"
        param["fdictname"] = dict.fdictname
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fdictno,fdictname,ftablename,ficon,fupddate,foperator,ficon2,ftype,fmark_num,fnumpre"
    sql = sql + " from s_ts_equipment_dict where 1=1"
    if dict.fdictname is not None:
        sql = sql + " and fdictname like concat('%', :fdictname ,'%')"
        param["fdictname"] = dict.fdictname
    if dict.order == "desc":
        sql = sql + " order by  fdictno desc "
    else:
        sql = sql + " order by  fdictno asc "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = dict.offset
    param["limit"] = dict.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fdictno,b.factionno,b.faction,b.fdescribe,b.fimage,b.findex,b.scene_flag"
    sql_3 = sql_3 + " from s_ts_equipment_dict a,s_ts_equipment_action b "
    sql_3 = sql_3 + " where a.fdictno=b.fdictno "
    if dict.fdictname is not None:
        sql_3 = sql_3 + " and fdictname like concat('%', :fdictname ,'%')"
        param["fdictname"] = dict.fdictname
    sql_3 = sql_3 + " order by b.fdictno,b.factionno"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fdictno'] = data[key][0]
        d1['fdictname'] = data[key][1]
        d1['ftablename'] = data[key][2]
        d1['ficon'] = data[key][3]
        d1['fupddate'] = data[key][4]
        d1['foperator'] = data[key][5]
        d1['ficon2'] = data[key][6]
        d1['ftype'] = data[key][7]
        d1['fmarknum'] = data[key][8]
        d1['fnumpre'] = data[key][9]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['factionno'] = data_detail[j1][1]
                d2['faction'] = data_detail[j1][2]
                d2['fdescribe'] = data_detail[j1][3]
                d2['fimage'] = data_detail[j1][4]
                d2['findex'] = data_detail[j1][5]
                d2['scene_flag'] = data_detail[j1][6]
                detail_list.append(d2)

        d1["actions"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": dict.limit,
                 "page": dict.pageIndex,
                 "pages": (count + dict.limit - 1) // dict.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class EquipmentActionCreate(BaseModel):
    """设备类型动作明细创建模型"""
    faction: str = Field(..., description="动作名称", min_length=1, max_length=50)
    fdescribe: str = Field("", description="动作描述", max_length=200)
    findex: int = Field(..., description="排序索引", ge=0)
    sceneFlag: int = Field(0, description="场景标识", ge=0, le=1)  # 0或1


class EquipmentDictCreate(BaseModel):
    """设备类型主表创建模型"""
    fdictname: str = Field(..., description="设备类型名称")
    foperator: str = Field(..., description="操作人")
    fupddate: datetime = Field(datetime.now(), description="更新日期")  # 格式：YYYY-MM-DD

    # 可选字段
    ftablename: str = Field("", description="表名")
    ficon: str = Field("", description="图标路径")
    ficon2: str = Field("", description="二级图标路径")
    ftype: str = Field("0", description="类型标识")
    fmarkNum: str = Field("0", description="标记数量")
    fnumpre: str = Field("", description="编号前缀")

    # 嵌套明细列表
    actions: List[EquipmentActionCreate] = Field(default=None, description="动作列表")
    fdictno: int = Field(None, description="设备编号,修改数据时填入")


@equipment_dict_router.post("/create", summary="新增设备类型", dependencies=[Depends(oauth2_scheme)])
def create_equipment_dict(dict: EquipmentDictCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select nextval('s_ts_equipment_dict_fdictno_seq') as last_value"
            res = db.execute(text(sql)).fetchall()
            fdictno = res[0][0]
            detail_list = []
            jsondata = dict.actions
            for action in jsondata:
                sql = "insert into s_ts_equipment_action(fdictno,faction,fdescribe,findex,scene_flag)"
                sql = sql + " values( :fdictno , :faction , :fdescribe , :findex , :sceneFlag );"
                db.execute(text(sql), {
                    "fdictno": fdictno,
                    "faction": action.faction,
                    "fdescribe": action.fdescribe,
                    "findex": action.findex,
                    "sceneFlag": action.sceneFlag
                })
                # 返回明细
                d2 = {}
                d2['fdictno'] = fdictno
                d2['faction'] = action.faction
                d2['fdescribe'] = action.fdescribe
                d2['findex'] = action.findex
                d2['sceneFlag'] = action.sceneFlag
                d2['fimage'] = None
                d2['factionno'] = 1
                detail_list.append(d2)

            sql = "insert into s_ts_equipment_dict(fdictno,fdictname,ftablename,ficon,fupddate,foperator,ficon2,ftype,fmark_num,fnumpre)"
            sql = sql + " values( :fdictno , :fdictname , :ftablename , :ficon ,:fupddate , :foperator , :ficon2 , :ftype , :fmarkNum , :fnumpre);"
            db.execute(text(sql), {
                "fdictno": fdictno,
                "fdictname": dict.fdictname,
                "ftablename": dict.ftablename,
                "ficon": dict.ficon,
                "fupddate": dict.fupddate,
                "foperator": dict.foperator,
                "ficon2": dict.ficon2,
                "ftype": dict.ftype,
                "fmarkNum": dict.fmarkNum,
                "fnumpre": dict.fnumpre,
            })

            # 返回
            d1 = {}
            d1['fdictno'] = fdictno
            d1['fdictname'] = dict.fdictname
            d1['ftablename'] = dict.ftablename
            d1['ficon'] = dict.ficon
            d1['fupddate'] = dict.fupddate
            d1['foperator'] = dict.foperator
            d1['ficon2'] = dict.ficon2
            d1['ftype'] = dict.ftype
            d1['fmarknum'] = dict.fmarkNum
            d1['fnumpre'] = dict.fnumpre
            d1["actions"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@equipment_dict_router.post("/update", summary="修改设备详情")
def update_equipment_dict(dict: EquipmentDictCreate, db: Session = Depends(get_db)):
    try:
        fdictno = dict.fdictno
        with db.begin():
            sql = "delete from s_ts_equipment_action where fdictno= :fdictno "
            db.execute(text(sql), {
                "fdictno": fdictno,
            })
            jsondata = dict.actions
            for action in jsondata:
                sql = "insert into s_ts_equipment_action(fdictno,faction,fdescribe,findex,scene_flag)"
                sql = sql + " values( :fdictno , :faction , :fdescribe , :findex , :sceneFlag );"
                db.execute(text(sql), {
                    "fdictno": fdictno,
                    "faction": action.faction,
                    "fdescribe": action.fdescribe,
                    "findex": action.findex,
                    "sceneFlag": action.sceneFlag
                })
            sql = "update s_ts_equipment_dict"
            sql = sql + " set fdictname= :fdictname ,ftablename= :ftablename "
            sql = sql + " ,ficon= :ficon ,ficon2= :ficon2 ,ftype= :ftype "
            sql = sql + ",fmark_num= :fmarkNum ,fnumpre= :fnumpre "
            sql = sql + " ,fupddate= :fupddate ,foperator= :foperator "
            sql = sql + " where fdictno= :fdictno "
            db.execute(text(sql), {
                "fdictno": fdictno,
                "fdictname": dict.fdictname,
                "ftablename": dict.ftablename,
                "ficon": dict.ficon,
                "ficon2": dict.ficon2,
                "ftype": dict.ftype,
                "fmarkNum": dict.fmarkNum,
                "fnumpre": dict.fnumpre,
                "fupddate": dict.fupddate,
                "foperator": dict.foperator
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@equipment_dict_router.post("/delete/{fdictno}", summary="删除设备")
def delete_equipment_dict(fdictno: int = Path(description="设备编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) from s_ts_equipment_info where fequipmenttype= :fdictno "
            data = db.execute(text(sql), {
                "fdictno": fdictno,
            }).scalar()
            if data > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from s_ts_equipment_action where fdictno= :fdictno "
                db.execute(text(sql), {
                    "fdictno": fdictno
                })
                sql = "delete from s_ts_equipment_dict where fdictno= :fdictno "
                db.execute(text(sql), {
                    "fdictno": fdictno
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
